-- { echoOn }
{% for join_algorithm in ['hash', 'grace_hash'] -%}
SET join_algorithm='{{ join_algorithm }}';
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5			0	0	\N
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 LEFT JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5			0	0	\N
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4			0	0	\N
SELECT t1.*, t2.* from t1 LEFT JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2			0	0	\N
key1	e	5	5	5			0	0	\N
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4			0	0	\N
SELECT t1.*, t2.* from t1 LEFT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 0;
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 LEFT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 1, enable_parallel_replicas=0;
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 LEFT JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
1	1	1	1	1	1
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a=2 OR (t2.a IN (SELECT a FROM t1 WHERE a = 3))) ORDER BY ALL;
key1	a	1	1	2	key1	C	3	4	5
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	C	3	4	5
key1	e	5	5	5	key1	C	3	4	5
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 INNER JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
SELECT t1.*, t2.* from t1 INNER JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	D	4	1	6
SELECT t1.*, t2.* from t1 INNER JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 0;
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 INNER JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 1, enable_parallel_replicas=0;
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 INNER JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
1	1	1	1	1	1
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.key = t2.key AND (t1.a=2 OR (t2.a IN (SELECT a FROM t1 WHERE a = 3))) ORDER BY ALL;
key1	a	1	1	2	key1	C	3	4	5
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	C	3	4	5
key1	e	5	5	5	key1	C	3	4	5
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 RIGHT JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
		0	0	\N	key3	a3	1	1	1
		0	0	\N	key4	F	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
SELECT t1.*, t2.* from t1 RIGHT JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
		0	0	\N	key1	A	1	2	1
		0	0	\N	key3	a3	1	1	1
		0	0	\N	key4	F	1	1	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	D	4	1	6
SELECT t1.*, t2.* from t1 RIGHT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 0;
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 RIGHT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 1, enable_parallel_replicas=0;
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 RIGHT JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
1	1	1	1	1	1
SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON t1.key = t2.key AND (t1.a=2 OR (t2.a IN (SELECT a FROM t1 WHERE a = 3))) ORDER BY ALL;
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	C	3	4	5
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	C	3	4	5
key1	e	5	5	5	key1	C	3	4	5
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* FROM t1 FULL JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5			0	0	\N
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 FULL JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
		0	0	\N	key3	a3	1	1	1
		0	0	\N	key4	F	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5			0	0	\N
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4			0	0	\N
SELECT t1.*, t2.* from t1 FULL JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
		0	0	\N	key1	A	1	2	1
		0	0	\N	key3	a3	1	1	1
		0	0	\N	key4	F	1	1	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2			0	0	\N
key1	e	5	5	5			0	0	\N
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4			0	0	\N
SELECT t1.*, t2.* from t1 FULL JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 0;
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 FULL JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 1, enable_parallel_replicas=0;
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	A	1	2	1
key1	c	3	2	1	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
key1	d	4	7	2	key1	A	1	2	1
key1	d	4	7	2	key1	B	2	1	2
key1	d	4	7	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key1	e	5	5	5	key1	A	1	2	1
key1	e	5	5	5	key1	B	2	1	2
key1	e	5	5	5	key1	C	3	4	5
key1	e	5	5	5	key1	D	4	1	6
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 FULL JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
1	1	1	1	1	1
SELECT t1.*, t2.* FROM t1 FULL JOIN t2 ON t1.key = t2.key AND (t1.a=2 OR (t2.a IN (SELECT a FROM t1 WHERE a = 3))) ORDER BY ALL;
		0	0	\N	key3	a3	1	1	1
key1	a	1	1	2	key1	C	3	4	5
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	b	2	3	2	key1	D	4	1	6
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	C	3	4	5
key1	e	5	5	5	key1	C	3	4	5
key2	a2	1	1	1			0	0	\N
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* FROM t1 SEMI LEFT JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 SEMI LEFT JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	c	3	2	1	key1	B	2	1	2
key1	d	4	7	2	key1	D	4	1	6
SELECT t1.*, t2.* from t1 SEMI LEFT JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	c	3	2	1	key1	D	4	1	6
SELECT t1.*, t2.* from t1 SEMI LEFT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 0;
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	A	1	2	1
key1	c	3	2	1	key1	A	1	2	1
key1	d	4	7	2	key1	A	1	2	1
key1	e	5	5	5	key1	A	1	2	1
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 SEMI LEFT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 1, enable_parallel_replicas=0;
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	A	1	2	1
key1	c	3	2	1	key1	A	1	2	1
key1	d	4	7	2	key1	A	1	2	1
key1	e	5	5	5	key1	A	1	2	1
key4	f	2	3	4	key4	F	1	1	1
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 SEMI LEFT JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
1	1	1	1	1	1
SELECT t1.*, t2.* FROM t1 SEMI LEFT JOIN t2 ON t1.key = t2.key AND (t1.a=2 OR (t2.a IN (SELECT a FROM t1 WHERE a = 3))) ORDER BY ALL;
key1	a	1	1	2	key1	C	3	4	5
key1	b	2	3	2	key1	A	1	2	1
key1	c	3	2	1	key1	C	3	4	5
key1	d	4	7	2	key1	C	3	4	5
key1	e	5	5	5	key1	C	3	4	5
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* FROM t1 SEMI RIGHT JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 SEMI RIGHT JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	C	3	4	5
key1	d	4	7	2	key1	D	4	1	6
SELECT t1.*, t2.* from t1 SEMI RIGHT JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
SELECT t1.*, t2.* from t1 SEMI RIGHT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 0;
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT t1.*, t2.* from t1 SEMI RIGHT JOIN t2 ON t1.key = t2.key and t1.c ORDER BY (t1.key, t1.attr, t2.key, t2.attr) SETTINGS query_plan_use_new_logical_join_step = 1, enable_parallel_replicas=0;
key1	a	1	1	2	key1	A	1	2	1
key1	a	1	1	2	key1	B	2	1	2
key1	a	1	1	2	key1	C	3	4	5
key1	a	1	1	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 SEMI RIGHT JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
1	1	1	1	1	1
SELECT t1.*, t2.* FROM t1 SEMI RIGHT JOIN t2 ON t1.key = t2.key AND (t1.a=2 OR (t2.a IN (SELECT a FROM t1 WHERE a = 3))) ORDER BY ALL;
key1	a	1	1	2	key1	C	3	4	5
key1	b	2	3	2	key1	A	1	2	1
key1	b	2	3	2	key1	B	2	1	2
key1	b	2	3	2	key1	D	4	1	6
key4	f	2	3	4	key4	F	1	1	1
{% endfor -%}
